-- EDGE tables SCRIPT

CREATE TABLE [dbo].[Document](
	[docid] [int] NOT NULL,
	[name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
	[docid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Path](
	[path_id] [int] NOT NULL,
	[path] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Path] PRIMARY KEY CLUSTERED 
(
	[path_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Edge](
	[docid] [int] NOT NULL,
	[id] [int] NOT NULL,
	[parent_id] [int] NULL,
	[end_desc_id] [int] NULL,
	[path_id] [int] NULL,
	[value] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Edge] PRIMARY KEY CLUSTERED 
(
	[docid] ASC,
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Attribute](
	[docid] [int] NOT NULL,
	[parent_id] [int] NOT NULL,
	[path_id] [int] NOT NULL,
	[value] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Attribute] PRIMARY KEY CLUSTERED 
(
	[docid] ASC,
	[parent_id] ASC,
	[path_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


--FOREIGN KEYS
/* No need to use FK because should not affect performance
 * And using FK, the table can't be truncated

ALTER TABLE [dbo].[Edge]  WITH NOCHECK ADD  CONSTRAINT [FK_Edge_Document] FOREIGN KEY([docid])
REFERENCES [dbo].[Document] ([docid])
ALTER TABLE [dbo].[Edge] CHECK CONSTRAINT [FK_Edge_Document]

ALTER TABLE [dbo].[Edge]  WITH NOCHECK ADD  CONSTRAINT [FK_Edge_Path] FOREIGN KEY([path_id])
REFERENCES [dbo].[Path] ([path_id])
ALTER TABLE [dbo].[Edge] CHECK CONSTRAINT [FK_Edge_Path]


ALTER TABLE [dbo].[Attribute]  WITH NOCHECK ADD  CONSTRAINT [FK_Attribute_Edge] FOREIGN KEY([docid], [parent_id])
REFERENCES [dbo].[Edge] ([docid], [id])
ALTER TABLE [dbo].[Attribute] CHECK CONSTRAINT [FK_Attribute_Edge]

ALTER TABLE [dbo].[Attribute]  WITH NOCHECK ADD  CONSTRAINT [FK_Attribute_Path] FOREIGN KEY([path_id])
REFERENCES [dbo].[Path] ([path_id])
ALTER TABLE [dbo].[Attribute] CHECK CONSTRAINT [FK_Attribute_Path]
*/

--INDEXES

CREATE UNIQUE NONCLUSTERED INDEX [IX_Path] ON [dbo].[Path] 
(
	[path] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge] ON [dbo].[Edge] 
(
	[parent_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge_1] ON [dbo].[Edge] 
(
	[end_desc_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Edge_2] ON [dbo].[Edge] 
(
	[path_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

